// This do file generates cross-correlation tables between indexes
// Jiro Yoshida
// 2014.02.17

cd "C:\Users\juy18\Downloads\rent_index\rri2003sample"
*cd "C:\Users\Jiro\SkyDrive\~Research\0 Own research\Rent Index\rri2003sample"

log using pwcorr_out, smcl replace

clear
*set mem 64m
set matsize 1000
set more off
set type double


foreach m in  ///
"Atlanta" ///
"Boston" ///
"Dallas" ///
"Detroit" ///
"Houston" ///
"Los_Angeles" ///
"Miami" ///
"San_Francisco" ///
"Seattle" ///
"Washington" ///
{
use `m'RRI_BLS_2003_Full, clear
mkcorr `m'_BLSA_r_n `m'_BLSAdj `m'_BLSFull `m'_BLSI `m'mean `m'mean2003sample `m'rshet `m'rshet2003sample, log(corr_`m') replace

}



//--------------------------------
// The following part estimate average correlations for all MSAs out of panel regressions
// The correlation coefficient between y and x is the square root of R2 for a bivariate regression of y on x


// appending all MSA data into one file
cd "C:\Users\juy18\Downloads\rent_index\rri2003sample"
use AtlantaRRI_BLS_2003_Full, clear

rename Atlanta_BLSA_r_n BLS_Actual
rename Atlanta_BLSAdj BLS_Adjusted
rename Atlanta_BLSFull BLS_S
rename Atlanta_BLSI BLS_S2003
rename Atlantamean Mean
rename Atlantamean2003sample Mean_2003
rename Atlantarshet RRI
rename Atlantarshet2003sample RRI_2003

drop Atlanta*

foreach m in  ///
"Boston" ///
"Dallas" ///
"Detroit" ///
"Houston" ///
"Los_Angeles" ///
"Miami" ///
"San_Francisco" ///
"Seattle" ///
"Washington" ///
{

append using `m'RRI_BLS_2003_Full
*capture noisily rename _merge _merge_`m'
*capture noisily drop _merge

replace BLS_Actual = `m'_BLSA_r_n if `m'_BLSA_r_n <.
replace BLS_Adjusted = `m'_BLSAdj if `m'_BLSA_r_n <.
replace BLS_S= `m'_BLSFull if `m'_BLSFull <.
replace BLS_S2003 = `m'_BLSI  if `m'_BLSI <.
replace Mean = `m'mean if `m'mean <.
replace Mean_2003 = `m'mean2003sample  if `m'mean2003sample <.
replace RRI = `m'rshet if `m'rshet <.
replace RRI_2003 = `m'rshet2003sample if `m'rshet2003sample <.

drop `m'*

}

save RRI_BLS_10MSA, replace


// generate average correlation coefficients for 10 MSAs
use RRI_BLS_10MSA, clear

tab msa_name4, gen(MSA)

//demean indexes
foreach y in BLS_Actual BLS_Adjusted BLS_S BLS_S2003 RRI RRI_2003 Mean Mean_2003 {
	egen mean_`y' = mean(`y'), by(msa_name4)
	gen `y'_demean = `y' - mean_`y'
	drop mean_`y' 
}

// run panel regressions
foreach y in BLS_Actual BLS_Adjusted BLS_S BLS_S2003 RRI RRI_2003 Mean Mean_2003 {
	foreach x in BLS_Actual BLS_Adjusted BLS_S BLS_S2003 RRI RRI_2003 Mean Mean_2003 {
		*capture noisily reg `y' `x' MSA*
		capture noisily reg `y'_demean `x'_demean MSA*
		gen sign=1
		capture replace sign=-1 if _b[`x'_demean]<0
		capture gen Corr`y'_on_`x'=sqrt(e(r2))*sign
		drop sign
	}
}

// reshape the table
keep Corr*
duplicates drop CorrBLS_Actual_on_BLS_Actual, force

reshape long Corr, i(CorrBLS_Actual_on_BLS_Actual) j(x) string
drop CorrBLS_Actual_on_BLS_Actual

split x, p("_on_")
drop x
sort x1 x2

reshape wide Corr, i(x1) j(x2) string

export excel pwreg_corr, firstrow(variables) replace
save pwreg_corr, replace

log close
